import pymysql


class DataBaseHandle(object):
    ''' 定义一个 MySQL 操作类'''
    def __init__(self):
        '''初始化数据库信息并创建数据库连接'''
        # 下面的赋值其实可以省略，connect 时 直接使用形参即可
        self.host = '116.62.51.1'
        self.username = 'hbUser'
        self.password = '123456'
        self.database = 'test'
        self.port = 3306
        self.db = pymysql.connect(self.host,
                                  self.username,
                                  self.password,
                                  self.database,
                                  self.port,
                                  charset='utf8')

    #  这里 注释连接的方法，是为了 实例化对象时，就创建连接。不许要单独处理连接了。
    #
    # def connDataBase(self):
    #     ''' 数据库连接 '''
    #
    #     self.db = pymysql.connect(self.host,self.username,self.password,self.port,self.database)
    #
    #     # self.cursor = self.db.cursor()
    #
    #     return self.db

    def insertDB(self, sql):
        ''' 插入数据库操作 '''
        self.cursor = self.db.cursor()
        try:
            # 执行sql
            # self.cursor.execute(sql)
            tt = self.cursor.execute(sql)  # 返回 插入数据 条数 可以根据 返回值 判定处理结果
            print(tt)
            self.db.commit()
            return tt
        except BaseException:
            print0("执行MySQL: %s 时出错：%s" % (sql, e))
            # 发生错误时回滚
            self.db.rollback()
        finally:
            self.cursor.close()

    def insertDBmany(self, sql, data):
        self.cursor = self.db.cursor()
        try:
            self.cursor.executemany(sql, data)
            self.db.commit()
        except Exception as e:
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            self.db.rollback()
        finally:
            self.cursor.close()

    def deleteDB(self, sql):
        ''' 操作数据库数据删除 '''
        self.cursor = self.db.cursor()

        try:
            # 执行sql
            self.cursor.execute(sql)
            # tt = self.cursor.execute(sql) # 返回 删除数据 条数 可以根据 返回值 判定处理结果
            # print(tt)
            self.db.commit()
        except Exception as e:
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # 发生错误时回滚
            self.db.rollback()
        finally:
            self.cursor.close()

    def updateDb(self, sql):
        ''' 更新数据库操作 '''

        self.cursor = self.db.cursor()

        try:
            # 执行sql
            self.cursor.execute(sql)
            # tt = self.cursor.execute(sql) # 返回 更新数据 条数 可以根据 返回值 判定处理结果
            # print(tt)
            self.db.commit()
        except Exception as e:
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            # 发生错误时回滚
            self.db.rollback()
        finally:
            self.cursor.close()

    def selectDb(self, sql):
        ''' 数据库查询'''
        self.cursor = self.db.cursor()
        try:
            self.cursor.execute(sql)  # 返回 查询数据 条数 可以根据 返回值 判定处理结果
            data = self.cursor.fetchall()  # 返回所有记录列表
            print(data)
            # 结果遍历
            for row in data:
                sid = row[0]
                name = row[1]
                # 遍历打印结果
                print('sid = %s,  name = %s' % (sid, name))
        except Exception as e:
            print("执行MySQL: %s 时出错：%s" % (sql, e))
        finally:
            self.cursor.close()

    def selectDb_2(self, sql):
        """查询数据"""
        # self.cursor = self.db.cursor()  # 以元组格式返回查询结果
        self.cursor = self.db.cursor(
            cursor=pymysql.cursors.DictCursor)  # 查以字典格式返回查询结果
        try:
            self.cursor.execute(sql)
            data = self.cursor.fetchall()
        except Exception as e:
            print("执行MySQL: %s 时出错：%s" % (sql, e))
            data = tuple()
        finally:
            self.cursor.close()
        return data

    def closeDb(self):
        ''' 数据库连接关闭 '''
        self.db.close()


if __name__ == '__main__':

    DbHandle = DataBaseHandle()
    # DbHandle.insertDB('insert into test(name) values ("%s")'%('FuHongXue'))
    #DbHandle.insertDB('insert into test(name) values ("%s")'%('FuHongXue'))
    h_id_s = DbHandle.selectDb_2(
        "SELECT id from cpu ORDER BY id DESC limit 1 ")
    #DbHandle.electDb_2("SELECT id from machine_hardware_information where hardware_ip='10.88.66.43' limit 1 ")

    print(h_id_s)
    result = h_id_s[0]['id']
    #DbHandle.insertDB("insert into cpu (h_id) values (%s)" % (result ))
    #DbHandle.updateDb('update test set name = "%s" where sid = "%d"' %('YeKai',22))
    #DbHandle.selectDb('select * from test')
    #DbHandle.insertDB('insert into test(name) values ("%s")'%('LiXunHuan'))
    #DbHandle.deleteDB('delete from test where sid > "%d"' %(25))
    #DbHandle.selectDb('select * from test')
    print("结束")
    DbHandle.closeDb()
